set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
with lmdm_sys_staff as (
    select
       staff_no as code,  --编码
       name,  --名字
       post_name,  --岗位名字
       post_code,  -- 岗位code
       network_id,  --网点id
       network_name,  --网点名字
   1 as  type1
     from jms_dim.dim_lmdm_sys_user
where  incumbency_status=1
  and is_delete=1
  and is_enable=1
  and post_code='0184'
  and staff_no not like 'LS%'
   and network_name  not like '%BEST%'
   ),
     staff_rank_detail as (
         select
             delivery_code
              , NULL AS delivery_name
              , create_day
              , sum(if(work_type = '普通工单' and second_type_name = '服务态度',1,0))  as fwtd_num --服务态度
              , sum(if(work_type = '平台工单' and
                       (deliver_type is not null and deliver_type <> ''),1,0)) as sfts_num   --三方投诉
              , sum(if((work_type = '普通工单' and second_type_name <> '服务态度') or
                       (work_type = '平台工单' and (deliver_type is null or deliver_type = '')),1,0)) as signts_num  --签收投诉
         from jms_dm.dm_waybill_customer_complaint_detail_dt
         where dt='{{ execution_date | cst_ds }}'
         and delivery_code is not null
           and is_delete = 2
           and work_type in ('普通工单', '平台工单')
         group by delivery_code , create_day
     ),
-- 末端时效  OK
     terminal_sign_summary_plan_sign as (
         select dt,                --业务日期
       final_sign_user_code,                                    --理货员code
       max(final_sign_user_name)    as final_sign_user_name,    --理货员名字
       sum(need_sign_count)         as need_sign_count,         -- 应签收
       sum(all_sign_22)     as all_sign_22,         --22点签收量
       coalesce(round((sum(all_sign_22) / sum(need_sign_count)) * 100, 2), 0.00) as isSignRate               -- 22点签收率
from jms_dm.dm_terminal_sign_punctuality_rate_dt
where dt >= trunc('{{ execution_date | cst_ds }}','MM')
  and dt<='{{ execution_date | cst_ds }}'
group by dt,final_sign_user_code
     ),
-- 全程时效分析-汇总表（时效签收维度
     duration_of_whole_process as (
           select
                dt as  sign_date,
                 deliver_user_code as deliver_later_user_code,
                 max(deliver_user_name) as deliver_later_user,
                 sum(deliver_aging_sign_time) as deliver_aging_sign_time,     -- 时效签收和末端网点派件时间差
                 sum(deliver_aging_sign_cnt) as deliver_aging_sign_cnt,       --时效签收和末端网点派件票数
          sum(deliver_aging_sign_time)/sum(deliver_aging_sign_cnt) as delver_sign_avg_hour
    from jms_dm.dm_whole_effect_deliver_summary_dt
        where dt>=trunc('{{ execution_date | cst_ds }}','MM')
          and dt<='{{ execution_date | cst_ds }}'
   group by dt,deliver_user_code
     ),
     -- 这个需要换表
 outfield_person_order as (
     select date_time,          --日期
       pick_staff_code,    -- 员工code
       max(pick_staff_name) as pick_staff_name,     -- 员工名字
       sum(taking_success_sum) as taking_success_sum,  --揽收成功量   *
       sum(order_taking_fail_sum) as order_taking_fail_sum,  --揽收失败量 *
     round(sum(taking_success_sum)/(sum(taking_success_sum)+sum(order_taking_fail_sum)),4) as taking_success_rate
    from (select
        dt as date_time --业务日期
       ,pick_staff_code   --取件业务员编码
       ,max(pick_staff_name)as pick_staff_name --取件业务员名字
       ,sum(if(order_status_code=103,1,0)) as taking_success_sum  --揽收成功总量
       ,sum(if(order_status_code=104,1,0)) as order_taking_fail_sum  --揽收失败总量
       ,dt  --分区
from jms_dwd.dwd_yl_oms_oms_order_incre_dt t1
join (select order_source_code
from jms_dim.dim_tab_order_source_group
    where order_source_group_id=100 )  t2
    on t1.order_source_code=t2.order_source_code
 where t1.dt>=trunc('{{ execution_date | cst_ds }}','MM')
   and t1.dt<='{{ execution_date | cst_ds }}'
group by dt,pick_staff_code
        ) t1
 group by date_time,pick_staff_code
     ),
     person_detail_info as (
         select
         delivery_code,
                NULL AS delivery_name ,
                date_time,
                sum(fwtd_num)as fwtd_num,
                sum(sfts_num)as sfts_num,
                sum(signts_num)as signts_num,
                sum(need_sign_count)as need_sign_count,
                sum(all_sign_22)as all_sign_22,
                sum(isSignRate)as isSignRate,
                sum(deliver_aging_sign_time)as deliver_aging_sign_time,
                sum(deliver_aging_sign_cnt)as deliver_aging_sign_cnt,
                sum(delver_sign_avg_hour)as delver_sign_avg_hour,
                sum(taking_success_sum)as taking_success_sum,
                sum(order_taking_fail_sum)as order_taking_fail_sum,
                sum(taking_success_rate)as taking_success_rate
         from (
        select
               delivery_code,  --派件员code
               delivery_name,  --派件员名字
               create_day as date_time ,     --业务日期
               fwtd_num,       -- 服务态度投诉票数
               sfts_num,      -- 三方投诉票数
               signts_num,     -- 签收投诉票数
               0 as  need_sign_count,  --应签收量
               0 as  all_sign_22,  --22点签收量
               0 as  isSignRate, --22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as  taking_success_sum,
                0 as  order_taking_fail_sum,
                0 as  taking_success_rate
         from staff_rank_detail
         union all
         select
                final_sign_user_code as delivery_code,
                final_sign_user_name as delivery_name,
                dt  as date_time,
                0 as   fwtd_num,       -- 服务态度投诉票数
                0 as   sfts_num,       -- 三方投诉票数
                0 as   signts_num,     -- 签收投诉票数
                need_sign_count,  --应签收量
                all_sign_22,  --22点签收量
                isSignRate, --22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as  taking_success_sum,
                0 as  order_taking_fail_sum,
                0 as  taking_success_rate


         from terminal_sign_summary_plan_sign
         union all
         select
                deliver_later_user_code  as delivery_code,
                deliver_later_user       as delivery_name ,
                sign_date  as date_time,
                0 as   fwtd_num,       -- 服务态度投诉票数
                0 as   sfts_num,       -- 三方投诉票数
                0 as   signts_num,     -- 签收投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as  taking_success_sum,
                0 as  order_taking_fail_sum,
                0 as  taking_success_rate

         from duration_of_whole_process
         union all
         select
                pick_staff_code  as delivery_code,
                pick_staff_name  as delivery_name,
                date_time as date_time,
                0 as   fwtd_num,       -- 服务态度投诉票数
                0 as   sfts_num,       -- 三方投诉票数
                0 as   signts_num,     -- 签收投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                taking_success_sum,
                order_taking_fail_sum,
                taking_success_rate
         from outfield_person_order
             ) tmp
         group by delivery_code,date_time
     ) ,
    star_ratings_info_base as(
        select   five_start_num   --五星人数
               ,four_start_num   --四星人数
               ,one_start_num   --一星人数
               ,national_mon_five_start_amt --全国月度五星金额
               ,national_mon_four_start_amt  -- 全国月度四星金额
               ,area_quarter_first_amt       --代理区季度第一名
               ,area_quarter_second_amt   --代理区季度第二名
               ,area_quarter_third_amt  --代理区季度第三名
               ,national_quarter_top_ten_amt  --全国季度前十名
               ,national_year_first_amt      --全国年度第一名
               ,national_year_second_amt  ---全国年度第二名
               ,national_year_third_amt   --全国年度第三名
               ,national_year_fourth_amt  --全国年度第4名
               ,national_year_fifth_amt  --全国年度第5名

              ,round(service_attitude_score/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_service_attitude_score_avg,  --服务态度配置分值
              round(trilateral_complaint_score/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_trilateral_complaint_score_avg, --三方投诉配置分值
              round(SIGN_COMPLAINT_SCORE/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_sign_complaint_score_avg,   --签收投诉配置分值
              round(prescription_sign_score/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_prescription_sign_score_avg, --时效签收率配置分值
              prescription_sign_full_score , --时效签收率配置满分值
              prescription_sign_zero_score , --时效签收率配置零分值
              round(dispatch_score/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_dispatch_score_avg, --派签时长配置分值
              dispatch_full_score , --派签时长配置满分值
              dispatch_zero_score , --派签时长配置零分值
              round(throwaway_collect_score/substr(last_day('{{ execution_date | cst_ds }}'),9,10),4) as day_throwaway_collect_score_avg, --散单揽收成功率配置分值
              throwaway_collect_full_score , --散单揽收成功率配置满分值
              throwaway_collect_zero_score , -- 散单揽收成功率配置零分值

              service_attitude_score,      -- 服务态度分值
              trilateral_complaint_score,  -- 三方投诉分值
               sign_complaint_score,    --签收投诉分值
               prescription_sign_score,  --时效签收率分值
               dispatch_score,           --派签时长分值
               throwaway_collect_score,  --散单揽收成功率分值

              1 as type1
        from jms_dim.dim_tab_ops_star_ratings_info_base
        where is_valid = 1
         ),
 network_whole_massage    as (
     select id,         --网点id
            code,       --网点code
            name,       --网点名字
            agent_code, --代理区code
            agent_name  --代理区名字
     from jms_dim.dim_network_whole_massage
     where  agent_name not like '%BEST%'
         )
insert overwrite table jms_dm.dm_start_rnk_day_detail_dt partition (dt)
select
                 code           --员工code
                ,name          --员工名字
                ,agent_code       --代理区code
                ,agent_name       --代理区
                ,nvl(day_service_quality_score,0)as day_service_quality_score      -- 服务态度得分
                ,nvl(day_trilateral_complaint_score,0)as day_trilateral_complaint_score --三方投诉得分
                ,nvl(day_sign_complaint_score,0)as day_sign_complaint_score       -- 签收投诉得分
                ,nvl(day_isSignRate_score,0)as day_isSignRate_score           --时效签收准点率  还需和配置表比较
                ,nvl(day_delver_sign_score,0)as day_delver_sign_score          --派签时长得分
                ,nvl(day_taking_success_score,0)as day_taking_success_score       --散单揽收成功率得分
                ,day_service_attitude_score_avg                                                                                  --服务态度配置分值
                ,day_trilateral_complaint_score_avg                                                                              --三方投诉配置分值
                ,day_sign_complaint_score_avg                                                                                    --签收投诉配置分值
                ,day_prescription_sign_score_avg    --时效签收率配置分值
                ,prescription_sign_full_score                                                                            --时效签收率配置满分值
                ,prescription_sign_zero_score                                                                            --时效签收率配置零分值
                ,day_dispatch_score_avg                                                                                          --派签时长配置分值
                ,dispatch_full_score                                                                                     --派签时长配置满分值
                ,dispatch_zero_score                                                                                     --派签时长配置零分值
                ,day_throwaway_collect_score_avg                                                                                 --散单揽收成功率配置分值
                ,throwaway_collect_full_score                                                                            --散单揽收成功率配置满分值
                ,throwaway_collect_zero_score-- 散单揽收成功率配置零分值
       ,(day_service_quality_score
                +day_trilateral_complaint_score
                +day_sign_complaint_score
                +day_isSignRate_score
                +day_delver_sign_score
                +day_taking_success_score) as score_sum    --总得分
       ,row_number() over (order by
           (day_service_quality_score
                +day_trilateral_complaint_score
                +day_sign_complaint_score
                +day_isSignRate_score
                +day_delver_sign_score
                +day_taking_success_score) desc ,isSignRate desc,delver_sign_avg_hour ,taking_success_rate desc) as country_rank  --全国排名
        ,row_number() over (partition by agent_code order by
           (day_service_quality_score
                +day_trilateral_complaint_score
                +day_sign_complaint_score
                +day_isSignRate_score
                +day_delver_sign_score
                +day_taking_success_score) desc,isSignRate desc,delver_sign_avg_hour,taking_success_rate desc ) as agent_rank  --代理区排名
              ,date_time
              ,nvl(need_sign_count,0) as need_sign_count   --时效签收应签收量
              ,all_sign_22 as aging_sign_in_time_count   --时效签收及时签收量
              ,nvl(deliver_aging_sign_time,0) as deliver_aging_sign_time  --派签时长
              ,nvl(deliver_aging_sign_cnt,0) as deliver_aging_sign_cnt--派签总量
              ,nvl(taking_success_sum,0) as taking_success_sum   --揽收成功量
              ,nvl(order_taking_fail_sum,0) as order_taking_fail_sum  --揽收失败量
               ,five_start_num   --五星人数
               ,four_start_num   --四星人数
               ,one_start_num   --一星人数
               ,national_mon_five_start_amt --全国月度五星金额
               ,national_mon_four_start_amt  -- 全国月度四星金额
               ,area_quarter_first_amt       --代理区季度第一名
               ,area_quarter_second_amt   --代理区季度第二名
               ,area_quarter_third_amt  --代理区季度第三名
               ,national_quarter_top_ten_amt  --全国季度前十名
               ,national_year_first_amt      --全国年度第一名
               ,national_year_second_amt  ---全国年度第二名
               ,national_year_third_amt   --全国年度第三名
               ,national_year_fourth_amt  --全国年度第4名
               ,national_year_fifth_amt  --全国年度第5名
              ,dt

from (
         select a.code,                                                                                                        --员工code
                a.name,                                                                                                        --员工名字
                 network.agent_code,                                                                                                  --代理区code                                                                                                 --代理区code
                 network.agent_name,                                                                                                  --代理区名字
                b.date_time                                                               as date_time,
                if(b.fwtd_num > 0, 0,round(day_service_attitude_score_avg, 4)) as day_service_quality_score,      -- 服务态度得分
                if(b.sfts_num > 0, 0,round(day_trilateral_complaint_score_avg, 4)) as day_trilateral_complaint_score, --三方投诉得分
                if(b.signts_num > 0, 0, round(day_sign_complaint_score_avg, 4)) as day_sign_complaint_score,       -- 签收投诉得分
                b.isSignRate,
                case when b.isSignRate  >= prescription_sign_full_score then round(day_prescription_sign_score_avg, 4)
                     when b.isSignRate  <= prescription_sign_zero_score then 0
                     else round((b.isSignRate  - prescription_sign_zero_score) /
                               (prescription_sign_full_score - prescription_sign_zero_score)
                                   * day_prescription_sign_score_avg, 4) end  as day_isSignRate_score,           --时效签收准点率  还需和配置表比较
                delver_sign_avg_hour,
                case
                    when delver_sign_avg_hour >= dispatch_zero_score then 0
                    when delver_sign_avg_hour <= dispatch_full_score then round(day_dispatch_score_avg, 4)
                    else round((dispatch_zero_score - delver_sign_avg_hour) /
                               (dispatch_zero_score - dispatch_full_score)
                                   * day_dispatch_score_avg, 4)
                    end                                                                     as day_delver_sign_score,          --派签时长得分
               taking_success_rate,
                case when taking_success_rate * 100 >= throwaway_collect_full_score
                        then round(day_throwaway_collect_score_avg, 4)
                    when taking_success_rate <= throwaway_collect_zero_score then 0
                    else round((taking_success_rate * 100 - throwaway_collect_zero_score) /
                               (throwaway_collect_full_score - throwaway_collect_zero_score)
                                   * day_throwaway_collect_score_avg, 4)
                    end                                                                     as day_taking_success_score,       --散单揽收成功率得分
                f.service_attitude_score as  day_service_attitude_score_avg,     --服务态度配置分值
                f.trilateral_complaint_score as day_trilateral_complaint_score_avg,                        --三方投诉配置分值
                f.sign_complaint_score as day_sign_complaint_score_avg,                                    --签收投诉配置分值
                f.prescription_sign_score as day_prescription_sign_score_avg, --时效签收率配置分值
                f.prescription_sign_full_score,                                                                            --时效签收率配置满分值
                f.prescription_sign_zero_score,                                                                            --时效签收率配置零分值
                f.dispatch_score as day_dispatch_score_avg,                                    --派签时长配置分值
                f.dispatch_full_score,                                                                                     --派签时长配置满分值
                f.dispatch_zero_score,                                                                                     --派签时长配置零分值
                f.throwaway_collect_score as day_throwaway_collect_score_avg,                   --散单揽收成功率配置分值
                f.throwaway_collect_full_score,                                                                            --散单揽收成功率配置满分值
                f.throwaway_collect_zero_score,-- 散单揽收成功率配置零分值
                b.need_sign_count,   --时效签收应签收量
                b.all_sign_22,   --时效签收及时签收量
                b.deliver_aging_sign_time,  --派签时长
                b.deliver_aging_sign_cnt,--派签总量
                b.taking_success_sum,   --揽收成功量
                b.order_taking_fail_sum,  --揽收失败量
                f.five_start_num   --五星人数
               ,f.four_start_num   --四星人数
               ,f.one_start_num   --一星人数
               ,f.national_mon_five_start_amt --全国月度五星金额
               ,f.national_mon_four_start_amt  -- 全国月度四星金额
               ,f.area_quarter_first_amt       --代理区季度第一名
               ,f.area_quarter_second_amt   --代理区季度第二名
               ,f.area_quarter_third_amt  --代理区季度第三名
               ,f.national_quarter_top_ten_amt  --全国季度前十名
               ,f.national_year_first_amt      --全国年度第一名
               ,f.national_year_second_amt  ---全国年度第二名
               ,f.national_year_third_amt   --全国年度第三名
               ,f.national_year_fourth_amt  --全国年度第4名
               ,f.national_year_fifth_amt  --全国年度第5名
               ,'{{ execution_date | cst_ds }}'  as dt
         from lmdm_sys_staff a
                  left join person_detail_info b
                            on a.code = b.delivery_code
--                   left join terminal_sign_summary_plan_sign c
--                             on a.code = c.final_sign_user_code
--                   left join duration_of_whole_process d
--                             on a.code = d.deliver_later_user_code
--                   left join outfield_person_order e
--                             on a.code = e.pick_staff_code
                   join network_whole_massage network
                             on a.network_id = network.id
                   cross join star_ratings_info_base f
                            on a.type1 = f.type1
     )tmp
distribute by dt , pmod(hash(rand()),10);